package com.zhl.oracle;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import org.junit.Test;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.concurrent.atomic.AtomicReference;

/**
 * @program: demos
 * @description:
 * @author: 刘振华
 * @create: 2020-10-11 16:01
 **/
public class OracleComment {
	private String path = "D:\\work\\文档\\3.塔里木\\设计\\设备数据管理与应用项目模型设计(Oracle).xlsx";
	public static Map<String,String> commentMap = new HashMap<>();
	private int start = "CREATE TABLE \"TARIM\".\"".length();
	@Test
	public void addComment()throws Exception{
		File excel = new File(path);
		//COMMENT ON COLUMN CD_BAS_EQU.PK_EQU_ID IS 'id';
		//1.将excel中读取成一个map
		EasyExcel.read(excel, CommentEo.class, new Field2Comment())
			.doReadAll();
//		System.out.println(commentMap);
//		commentMap.put("UPDATE_TIME","更新时间");
//		commentMap.put("CREATE_TIME","创建时间");
//		commentMap.put("CREATOR","创建人");
//		commentMap.put("","");
		//2.将数据库中的字段读取出来
		File txt = new File("D:\\work\\文档\\3.塔里木\\数据库\\sql\\zy-traim.sql");
		BufferedReader bfr = new BufferedReader(new FileReader(txt));
		AtomicReference<String> tableName = new AtomicReference<>();
		AtomicBoolean flag = new AtomicBoolean(false);
		AtomicInteger times = new AtomicInteger(0);
		final List<String> noComments = new ArrayList<>();
		bfr.lines().forEach(line->{
			if(line.startsWith("CREATE TABLE")){
				flag.set(true);
				tableName.set(line.substring(start, line.length() - 3));
				return;
			}
			if(")".equals(line)){
				flag.set(false);
			}
			if(flag.get()){
				String columnName = line.split("\"")[1];
				if(commentMap.containsKey(columnName)) {
					String tname = columnName;
					if("LEVEL".equals(columnName)) {
						tname = "\""+columnName+"\"";
					}
					System.out.println("COMMENT ON COLUMN " +
						tableName + "." + tname + " IS " + "'"+commentMap.get(columnName)+"';");
					times.set(times.intValue()+1);
				}else{
					noComments.add(tableName+"."+columnName);
				}
			}
		});
		System.out.println("=============一共找到"+times.get()+"条注释，以下为没找到注释的字段:"+noComments.size()+"==============");
		noComments.forEach(System.err::println);
	}
	@Test
	public void getFk()throws Exception{
//		EasyExcel.read(new File(path),CommentEo.class,new Field2Comment()).doReadAll();
//		list.forEach(s -> {
//			System.out.println(s+"\t"+"FK_EQU_ID"
//				+"\t"+"FK_EQU_UID");
//		});
		File txt = new File("D:\\work\\文档\\3.塔里木\\数据库\\sql\\业务表.sql");
		BufferedReader bfr = new BufferedReader(new FileReader(txt));
		bfr.lines().forEach(line->{
			if(line.startsWith("CREATE TABLE")){
				String tableName = line.substring(start, line.length() - 3);
//				if(list.contains(tableName)){
//					System.out.println(tableName+"\t"+"FK_EQU_ID"
//						+"\t"+"FK_EQU_UID");
//				}
				System.out.println(tableName);
			}
		});
	}
	@Test
	public void addTableComment(){
		File excel = new File(path);
		//COMMENT ON TABLE T_BLOG IS '博客表';
		//1.将excel中读取成一个map
		ExcelReader excelReader = EasyExcel.read(excel, CommentEo.class, new Field2Comment()).build();
		excelReader.excelExecutor().sheetList().stream().skip(1).forEach(sh->{
			String[] split = sh.getSheetName().split("\\(");
			System.out.println("COMMENT ON TABLE \""+split[0].trim()+"\" IS '" +
				split[1].split("\\)")[0].trim() +"';");
		});
	}
	@Test
	public void test1(){
		String str = "CREATE TABLE \"TARIM\".\"CD_BAS_BOILER\" (";
		System.out.println(str.substring(start,str.length()-3));
	}
}
